package cn.edu.ptu.jobfairs.core.utils;

import cn.edu.ptu.jobfairs.basic.annotations.CRC32;
import cn.edu.ptu.jobfairs.basic.annotations.Table;
import cn.edu.ptu.jobfairs.basic.common.Condition;
import cn.edu.ptu.jobfairs.basic.common.QueryCondition;
import cn.edu.ptu.jobfairs.basic.common.VisibleException;
import cn.edu.ptu.jobfairs.core.config.Consts;
import com.google.common.base.CaseFormat;
import lombok.extern.slf4j.Slf4j;
import lombok.val;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.*;

import static cn.edu.ptu.jobfairs.basic.common.Errors.SERVER_ERROR;


/**
 * Created by Lin Chenxiao on 2020-05-23
 **/
@Slf4j
public class SqlBuilder {
    final private StringBuilder command;
    private int index;
    private boolean useWhere = false;

    private SqlBuilder(String command) {
        this.command = new StringBuilder(command);
        index = 0;
    }

    public static SqlBuilder from(String command) {
        if (StringUtils.isEmpty(command)) throw new VisibleException(SERVER_ERROR, "sql内部初始化参数出错");
        return new SqlBuilder(command);
    }

    public SqlBuilder concat(Object command) {
        this.command.append(command);
        return this;
    }

    public SqlBuilder toCount() {
        int i = command.indexOf(" from ");
        return i > 7 ? toCount(i) : toCount(8);
    }

    private SqlBuilder toCount(int end) {
        int start = 7;
        int i = command.indexOf(" group by ");
        String s;
        if (i > 0) {
            i += 10;
            int j = command.indexOf(" ", i);
            s = j > i ? command.substring(i, j) : command.substring(i);
            s = "count(distinct " + s + ")";
            command.delete(i - 10, command.length());
        }
        else {
            s = "count(1)";
            i = command.indexOf(" order by ");
            if (i < 0) i = command.indexOf(" limit ");
            if (i > 0) command.delete(i, command.length());
        }
        command.replace(start, end, s);
        if (index < start) return this;
        if (index <= end) index = start + s.length();
        else index += s.length() + start - end;
        return this;
    }

    public SqlBuilder and() {
        if (useWhere) command.append(" and #");
        else {
            command.append(" where #");
            useWhere = true;
        }
        return this;
    }

    public SqlBuilder columns(String... fields) {
        StringBuilder s = new StringBuilder();
        for (String v : fields) {
            if (s.length() > 0) s.append(',');
            s.append(column(v));
        }
        append(s.toString());
        return this;
    }

    public SqlBuilder append(String param) {
        if (StringUtils.isEmpty(param)) return this;
        int i = command.indexOf("#", index);
        if (i == -1) throw new VisibleException(SERVER_ERROR, "sql内部追加参数[String]出错");
        command.replace(i, i + 1, param);
        index = i + param.length();
        return this;
    }

    public SqlBuilder append(Object obj) {
        return append(quote(obj));
    }

    public SqlBuilder append(SqlBuilder obj) {
        return append(obj.toString());
    }

    public SqlBuilder append(Class clazz) {
        return append(getTable(clazz));
    }

    public SqlBuilder append(Class clazz, String... fields) {
        String s = getTable(clazz);
        StringBuilder sb = new StringBuilder(s);
        for (int i = 0; i < fields.length; i++) {
            if (i == 0) sb.append('.').append(column(fields[i]));
            else sb.append(',').append(s).append('.').append(column(fields[i]));
        }
        return append(sb.toString());
    }

    public SqlBuilder insert(Object obj) {
        if (obj == null) return this;
        Object item = obj;
        Iterator iterator = null;
        if (obj instanceof Collection) {
            iterator = ((Collection) obj).iterator();
            if (!iterator.hasNext()) return this;
            item = iterator.next();
            if (item == null) return this;
        }
        try {
            int i = command.indexOf("#", index);
            if (i == -1) throw new VisibleException(SERVER_ERROR, "sql内部追加参数[insert]出错");
            command.deleteCharAt(i);
            command.insert(i++, "(");

            List<Field> fieldList = new LinkedList<>();
            for (Field field : getFields(item.getClass())) {
                String name = field.getName();
                if ("id".equals(name) || Collection.class.isAssignableFrom(field.getType())) continue;
                field.setAccessible(true);
                fieldList.add(field);
                i += insert(column(name), i);
                if (field.isAnnotationPresent(CRC32.class)) {
                    i += insert(columnCrc(name), i);
                }
            }
            command.deleteCharAt(--i);
            command.insert(i, ") values ");
            i += 9;
            while (item != null) {
                command.insert(i++, "(");
                for (Field field : fieldList) {
                    i += insert(quote(field.get(item)), i);
                    if (field.isAnnotationPresent(CRC32.class)) {
                        i += insert(quoteCrc(field.get(item)), i);
                    }
                }
                command.deleteCharAt(--i);
                command.insert(i, "),");
                i += 2;
                if (iterator != null && iterator.hasNext()) item = iterator.next();
                else break;
            }
            command.deleteCharAt(--i);
            index = i;
        }
        catch (Exception e) {
            log.error("insert 反射错误", e);
            throw new VisibleException(SERVER_ERROR, "sql内部追加参数[insert]出错, 由反射引发");
        }
        return this;
    }

    private int insert(String val, int idx) {
        command.insert(idx, val);
        command.insert(idx + val.length(), ',');
        return val.length() + 1;
    }

    public SqlBuilder update(Object obj) {
        if (obj == null) return this;
        try {
            int i = command.indexOf("#", index);
            if (i == -1) throw new VisibleException(SERVER_ERROR, "sql内部追加参数[update]出错");
            command.deleteCharAt(i);
            for (Field field : getFields(obj.getClass())) {
                String name = field.getName();
                if ("id".equals(name) || Collection.class.isAssignableFrom(field.getType())) continue;
                field.setAccessible(true);
                Object v = field.get(obj);
                if (v == null) continue;
                i = update(column(name), quote(v), i);
                if (field.isAnnotationPresent(CRC32.class)) {
                    i = update(columnCrc(name), quoteCrc(v), i);
                }
            }
            command.deleteCharAt(--i);
            index = i;
        }
        catch (Exception e) {
            log.error("update 反射错误", e);
            throw new VisibleException(SERVER_ERROR, "sql内部追加参数[update]出错, 由反射引发");
        }
        return this;
    }

    private int update(String column, String val, int idx) {
        idx = equals(column, val, idx);
        command.insert(idx++, ',');
        return idx;
    }

    public SqlBuilder where(Integer id) {
        where("id", String.valueOf(id));
        return this;
    }

    public SqlBuilder where(String field, Object val) {
        where(column(field), quote(val));
        return this;
    }

    public SqlBuilder where(Class clazz, String field, Object val) {
        where(column(clazz, field), quote(val));
        return this;
    }

    public SqlBuilder whereCrc(String field, Object val) {
        where(columnCrc(field), quoteCrc(val));
        command.insert(index, " and ");
        index = equals(column(field), quote(val), index + 5);
        return this;
    }

    public SqlBuilder whereCrc(Class clazz, String field, Object val) {
        where(columnCrc(clazz, field), quoteCrc(val));
        command.insert(index, " and ");
        index = equals(column(field), quote(val), index + 5);
        return this;
    }

    private void where(String column, String val) {
        int i = command.indexOf("#", index);
        if (i == -1) throw new VisibleException(SERVER_ERROR, "sql内部追加参数[where]出错");
        command.deleteCharAt(i);
        index = equals(column, val, i);
    }

    public SqlBuilder between(Class clazz, String field, Object val1, Object val2) {
        int i = command.indexOf("#", index);
        if (i == -1) throw new VisibleException(SERVER_ERROR, "sql内部追加参数[between]出错");
        command.deleteCharAt(i);
        String s = (clazz == null ? column(field) : column(clazz, field)) +
                " between " + quote(val1) + " and " + quote(val2);
        command.insert(i, s);
        index = i + s.length();
        return this;
    }

    public SqlBuilder between(String field, Object val1, Object val2) {
        return between(null, field, val1, val2);
    }

    public SqlBuilder in(Collection val) {
        return in(val, "`id`", false);
    }

    public SqlBuilder notIn(Collection val) {
        return in(val, "`id`", true);
    }

    public SqlBuilder in(Class clazz, String field, Collection val) {
        return in(clazz, field, val, false);
    }

    public SqlBuilder notIn(Class clazz, String field, Collection val) {
        return in(clazz, field, val, true);
    }

    public SqlBuilder in(Class clazz, String field, Collection val, boolean not) {
        return in(val, getTable(clazz) + '.' + column(field), not);
    }

    public SqlBuilder in(String field, Collection val) {
        return in(val, column(field), false);
    }

    public SqlBuilder notIn(String field, Collection val) {
        return in(val, column(field), true);
    }

    public SqlBuilder in(String tAlias, String field, Collection val) {
        return in(val, tAlias + "." + column(field), false);
    }

    private SqlBuilder in(Collection val, String column, boolean not) {
        if (CollectionUtils.isEmpty(val)) return this;
        int i = command.indexOf("#", index);
        if (i == -1) throw new VisibleException(SERVER_ERROR, "sql内部追加参数[in]出错");
        command.deleteCharAt(i);
        String s = column + (not ? " not " : "") + " in (";
        command.insert(i, s);
        i += s.length();
        for (Object v : val) {
            s = quote(v);
            command.insert(i, s);
            i += s.length();
            command.insert(i++, ',');
        }
        command.deleteCharAt(--i);
        command.insert(i++, ')');
        index = i;
        return this;
    }

    private int equals(String column, String val, int idx) {
        command.insert(idx, column);
        idx += column.length();
        command.insert(idx++, '=');
        command.insert(idx, val);
        return idx + val.length();
    }

    @Override
    public String toString() {
        return command.toString();
    }

    private static List<Field> getFields(Class clazz){
        List<Field> list = new LinkedList<>();
        while (clazz != Object.class) {
            list.addAll(Arrays.asList(clazz.getDeclaredFields()));
            clazz = clazz.getSuperclass();
        }
        return list;
    }

    private static String getTable(Class clazz) {
        if (clazz == null) return null;
        Table table = (Table) clazz.getDeclaredAnnotation(Table.class);
        if (table == null) return null;
        return table.value();
    }

    private static String column(String fieldName) {
        return fieldName == null ? "" : '`' + CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fieldName) + '`';
    }

    private static String columnCrc(String fieldName) {
        return fieldName == null ? "" : '`' + CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fieldName) + "_crc`";
    }

    private static String column(Class clazz, String fieldName) {
        return getTable(clazz) + '.' + column(fieldName);
    }

    private static String columnCrc(Class clazz, String fieldName) {
        return getTable(clazz) + '.' + columnCrc(fieldName);
    }

    private static String quote(Object val) {
        return val == null ? "null" : '\'' + replace(val) + '\'';
    }

    private static String quoteCrc(Object val) {
        return val == null ? "null" : "crc32('" + replace(val) + "')";
    }

    private static String replace(Object val) {
        return val.toString().replace("\\", "\\\\").replace("'", "\\'");
    }
}
